<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{
    var $_total = null;
    var $_pagination = null;
    
    public function __construct(){
    	parent::__construct();
 
        $mainframe = JFactory::getApplication();
 
        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('com_info_leader.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
    }
        /**
	*Get all list leader
	*/
	function getAll($list = null){

      /* $qr = "SELECT DISTINCT COCH_LKDT_CODE_LIST,COCH_CD,COCH_HUMAN_CODE_LIST,COCH_NAME,COCH_CODE,OCBA_CODE, OCBA_NAME,COCH_WOKF,COCH_FLAG,COCH_MAIK,COCH_MAKF 
              FROM coch
              LEFT JOIN (coli INNER JOIN cocm ON coli.COLI_COCM_CODE=cocm.COCM_CODE) ON coch.COCH_CODE = coli.COLI_COCH_CODE
              LEFT JOIN (ocba INNER JOIN (town INNER JOIN (area INNER JOIN pref ON area.AREA_CODE=pref.PREF_AREA_CODE) ON town.TOWN_PREF_CODE =pref.PREF_CODE)
                                ON ocba.OCBA_TOWN_CODE=town.TOWN_CODE) ON coch.COCH_OCBA_CODE=ocba.OCBA_CODE
              ";*/
              $qr="SELECT DISTINCT COCH_LKDT_CODE_LIST,COCH_CD,COCH_HUMAN_CODE_LIST,COCH_NAME,COCH_CODE,OCBA_CODE, OCBA_NAME,COCH_WOKF,COCH_FLAG,COCH_MAIK,COCH_MAKF, COCH_LDATE
                    FROM coch 
                    LEFT JOIN (SELECT OCBA_CODE, OCBA_NAME,PREF_AREA_CODE,PREF_CODE  FROM ocba 
                    INNER JOIN town ON OCBA_TOWN_CODE=TOWN_CODE 
                    INNER JOIN pref ON TOWN_PREF_CODE =PREF_CODE) AS ocba ON COCH_OCBA_CODE=OCBA_CODE                    
                    ";
                    
        $where = array();
        //search
       if(!empty($list['namecc'])){
            $where[] = "OCBA_NAME LIKE '%$list[namecc]%'";
       }
       if(!empty($list['human_rs'])){
            $where[] = "COCH_HUM1 LIKE '%$list[human_rs]%' OR COCH_HUM2 LIKE '%$list[human_rs]%' OR COCH_HUM LIKE '%$list[human_rs]%'
                                        OR COCH_HUM4 LIKE '%$list[human_rs]%' OR COCH_HUM5 LIKE '%$list[human_rs]%' OR COCH_HUM6 LIKE '%$list[human_rs]%'";
       }
       if(!empty($list['ld_meeting'])){
            $where[] = "COCH_LDME LIKE '%$list[ld_meeting]%'";
       }
       if(($list['area']) !=0 ){
         $where[] = "PREF_AREA_CODE =$list[area]";  
       }
       
       if($list['pref'] != 0){
            $where[] = "PREF_CODE =$list[pref]";
           
       }
       
       if($list['OCBA_CODE'] != null){
            $where[] = "OCBA_CODE = '$list[OCBA_CODE]'";
       }
       
       
       if(!empty($list['nameld'])){
            $where[] = "(COCH_NAME LIKE '%$list[nameld]%' OR COCH_KANA LIKE '%$list[nameld]%') ";
       }
       if(!empty($list['center'])){
            if ($list['center']==1)
            {
                $where[] = "LEFT(COCH_OCBA_CODE,1) =0";
            }
            else if($list['center']==2)
            {
                $where[] = "LEFT(COCH_OCBA_CODE,1) =1";
            }
            
       }

       if ($list['ls_date']!=0)
       {
            $now = getdate(); 
            $currentDate =  $now["year"] . "-" . $now["mon"] . "-" . $now["mday"]; 
            if ($list['ls_date']==1)
            {
                $where[] = "COCH_LDATE >= '".$currentDate."'";
            }
            else if($list['ls_date']==2)
            {
                $where[] = "COCH_LDATE < '".$currentDate."'";
            }
       }
       
       if(!empty($list['term'])){
        if(($list['term'])!=0)
            {
               $where[] = "COLI_TERM = $list[term]";
            }
           
            
       }
        if ($list['unit']==1)
        {
            $where[] = "COLI_UNIT =1";
        }
        else if($list['unit']==2)
        {
            $where[] = "COLI_UNIT =2";
        }
        else if($list['unit']==3)
        {
            $where[] = "COLI_UNIT =3";
        }
        else if($list['unit']==0)
        {
            //$where[] = "";
        }
        
        $wherestr="";
        for($i=1;$i<HelperBG::getIDAuto('cocm','COCM_CODE');$i++) {
            if(isset($list['chb'.$i])){
                if($wherestr==""){
                    $wherestr = $i;
                }else{
                    $wherestr =$wherestr.",".$i;
                }
            }
        }
       
        if(!empty($wherestr)){
            $where[] = "COLI_COCM_CODE in (".$wherestr.")";
        }
        
        // search LIST LKDT
        $wherestrcb="";
        for($i=1;$i<HelperBG::getIDAuto('lkdt','LKDT_CODE');$i++) {
            if(isset($list['chll'.$i])){
                if($wherestrcb==""){
                    $wherestrcb = $i;
                }else{
                    $wherestrcb =$wherestrcb.",".$i;
                }
            }   
        }
      $where1=array();
       if(($wherestrcb)!=null){
            $ar=explode(',',$wherestrcb);
            foreach ($ar as $value)
                {
                    $where1[] = "COCH_LKDT_CODE_LIST LIKE '%$value%'";
                }
            
        }
         
        if(!empty($where1)){
            $where1 = implode(' OR ', $where1);
             $where[]="(".$where1.")";
        }


        
        // search LIST HUMAN
        $wherestrch="";
        for($i=1;$i<HelperBG::getIDAuto('human','HUMAN_CODE');$i++) {
            if(isset($list['chhh'.$i])){
                if($wherestrch==""){
                    $wherestrch = $i;
                }else{
                    $wherestrch =$wherestrch.",".$i;
                }
            }   
        }
      $where2=array();
       if(($wherestrch)!=null){
            $ar=explode(',',$wherestrch);
            foreach ($ar as $value)
                {
                    $where2[] = "COCH_HUMAN_CODE_LIST LIKE '%$value%'";
                }
            
        }
        
        if(!empty($where2)){
            $where2 = implode(' OR ', $where2);
             $where[]="(".$where2.")";
        }

       if($where!=null){
            $where = ' WHERE '.implode(' AND ', $where);
        }else{
            $where = null;
        }
       // get total number record
       $this->_total = $this->getTotal($where);
         
       if(strpos($where, "COLI_") != FALSE){
          $qr .= "LEFT JOIN (SELECT COLI_TERM,COLI_UNIT,COLI_COCH_CODE,COLI_COCM_CODE,COLI_JDATE  FROM coli) AS coli ON COCH_CODE = COLI_COCH_CODE ";
       }

       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY COCH_CODE DESC "; 
       /* $qr .= " ORDER BY COCH_DATE DESC, COCH_CODE DESC "; */
        //echo $qr;die;
       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
        //load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}

    /**
	*Get total leader
	*/
    function getTotal($where = null){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
            
            /*$query = "SELECT count(DISTINCT COCH_CODE) FROM coch
              LEFT JOIN (coli INNER JOIN cocm ON coli.COLI_COCM_CODE=cocm.COCM_CODE) ON coch.COCH_CODE = coli.COLI_COCH_CODE
              LEFT JOIN (ocba INNER JOIN (town INNER JOIN (area INNER JOIN pref ON area.AREA_CODE=pref.PREF_AREA_CODE) ON town.TOWN_PREF_CODE =pref.PREF_CODE)
                                ON ocba.OCBA_TOWN_CODE=town.TOWN_CODE) ON coch.COCH_OCBA_CODE=ocba.OCBA_CODE $where";*/
              $query = "SELECT COUNT(DISTINCT COCH_CODE)             
                        FROM coch 
                        LEFT JOIN (SELECT OCBA_CODE, OCBA_NAME,PREF_AREA_CODE,PREF_CODE  FROM ocba 
                        INNER JOIN town ON OCBA_TOWN_CODE=TOWN_CODE 
                        INNER JOIN pref ON TOWN_PREF_CODE =PREF_CODE) AS ocba ON COCH_OCBA_CODE=OCBA_CODE                    
                        ";
            if(strpos($where, "COLI_") != FALSE){
               $query .= "LEFT JOIN (SELECT COLI_TERM,COLI_UNIT,COLI_COCH_CODE,COLI_COCM_CODE,COLI_JDATE  FROM coli) AS coli ON COCH_CODE = COLI_COCH_CODE ";
            }

            $query .= $where;
          //echo $query;
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        
        return $this->_total;
    } 
    
       /**
	*get pagination
	*/
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
    
        /**
	*delete leader by id
	*/
      public function remove($cid){
        $cid = implode(',', $cid);
       //return $result;
    	$query = "DELETE FROM coch WHERE COCH_CODE IN($cid)";
        $this->_db->setQuery($query);
        if($this->_db->query()){
            return true;
        }else{
            return false;
        }
    }
     /**
     * Check COCH_CD exist
    **/
    public function checkExistCOCH_CD($COCH_CD){
        $qr = "SELECT COCH_CD FROM coch WHERE COCH_CD = '$COCH_CD'";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
        if(!empty($result)){
            return true;
        }
        return false;
    }
    
     /**
	*save data
	*/
        public function savetemp($data, $addNew = false){
        	$table = & JTable::getInstance('bg','bgTable');
            $table->bind($data);
            
            if($table->store(false, $addNew)){
                return true;
            }
            
            return false;
        }
    
    //Check ID exist
    public function checkExistID($cid){
        $qr = "SELECT COCH_CODE FROM coch WHERE COCH_CODE = '$cid'";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
        if(!empty($result)){ 
            return true;
        }
        return false;
    }
    
        /**
	*get max leader
	*/
    function getMaxCOCH_CODE($where = null){
        // Load the content if it doesn't already exist
        $query = "SELECT MAX(COCH_CODE) FROM coch";
            
        $this->_db->setQuery($query);
        $this->_max = $this->_db->loadResult();
        
        return $this->_max;
    }
    
      /**
	*get max coch_cd leader
	*/
    function getMaxCOCH_CD($where = null){
        // Load the content if it doesn't already exist
        $query = "SELECT MAX(COCH_CD) FROM coch";
            
        $this->_db->setQuery($query);
        $this->_max = $this->_db->loadResult();
        
        return $this->_max;
    }
    
    
        /**
	*get all user
	*/
    function getAllUser($list = null){

	    $qr = "SELECT id,name,username,email from #__users";
     
       $qr .= " ORDER BY id DESC"; 

        
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    
       /**
	*get all jobm
	*/
    function getAllJobmList()
    {
        $qr="select JOBM_CODE,JOBM_NAME FROM jobm ORDER BY JOBM_CODE DESC";
        //Load data
        $this->_db->setQuery($qr);
        $result=$this->_db->loadObjectList();
        return $result;        
    }
    
        /**
	*get all pref
	*/
    function getAllPrefList()
    {
        $qr="select PREF_CODE,PREF_NAME FROM pref ORDER BY PREF_CODE ASC";
        $this->_db->setQuery($qr);
        $result=$this->_db->loadObjectList();
        return $result;
    }
    
        /**
	*GET ALL LIST USER WITH PARAMETER
	*/
    
   function getAllListUser($list = null){
    
	   $qr = "SELECT * from #__users";
       // search
       if(!empty($list['USERNAME'])){
            $where = " WHERE username LIKE '%$list[USERNAME]%' OR id = '$list[USERNAME]'";
       }else{
            $where = null;
       }
       
       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY id DESC"; 

       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    
   
    function load($cid){
        $table = & JTable::getInstance('bg','bgTable');
        $table->load($cid);
        return $table;
    }
    
    
    /**
	*GET ALL LIST USER WITH PARAMETER
	*/
    function loadlistuser($cid){
      
        $query = "SELECT USIN_USER_CODE,name FROM usin,#__users 
                         WHERE  #__users.id=usin.USIN_USER_CODE AND USIN_INFO_CODE=($cid)";
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
       // echo ($result->info_body);
        return $result;
        
    }
    
    /**
	*GET ALL OCBA
	*/
    function getAllOCBA(){

	   $qr = "SELECT OCBA_CODE,OCBA_NAME FROM ocba";
       // order by
       $qr .= " ORDER BY OCBA_CODE DESC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	} 
    /**
	*GET ALL AREA
	*/
    function getAllArea(){

	   $qr = "SELECT AREA_CODE,AREA_NAME FROM area ORDER BY AREA_CODE ASC";
       // order by
       //$qr .= " ORDER BY AREA_CODE DESC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	} 
    
    /**
	*GET ALL COCM
	*/
    function getAllCocm(){

	   $qr = "SELECT COCM_CODE,COCM_NAME FROM cocm";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}  
    
    
    //get all data from Business cooperation
    function getAllLkdt(){

	   $qr = "SELECT * FROM lkdt";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}  
    function getTotalLkdt(){
        $qr = "SELECT count(*) FROM lkdt";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadResult();
       return $result;
    }
    
    
    
    //get all data from Business cooperation
    function getAllHuman(){
	   $qr = "SELECT * FROM human";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}  
    function getTotalHuman(){
        $qr = "SELECT count(*) FROM human";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadResult();
       return $result;
    }
    
    
    //get total cocm
    function getTotalCocm(){
        $qr = "SELECT count(*) FROM cocm";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadResult();
       return $result;
    }


/**
	*GET ALL PREF BY AREA_CODE
	*/
    function getAllPref($area_code){
        if ($area_code==0)
        {
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref";
        }
        else
        {
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref WHERE PREF_AREA_CODE=".$area_code;
        }
           $this->_db->setQuery($qr);
           $result = $this->_db->loadObjectList();
           return $result;
	}
    
    /**
	*GET ALL LIST CENTER WITH PARAMETER
	*/
    function getAllListCenter($list = null){
    
	   $qr = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA FROM ocba WHERE OCBA_FLAG=1";
      // $list['OCBA_NAME']=null;
       $keyword = JRequest::getVar('OCBA_NAME');
       // search
       if(!empty($list['OCBA_NAME'])){
            $where = " AND OCBA_NAME LIKE '%$keyword%' OR OCBA_CODE LIKE '%$keyword%' ";
       }else{
            $where = null;
       }
       $qr.=$where;
       // order by
       $qr .= " ORDER BY OCBA_CODE ASC"; 

       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    /**
	*GET ALL COLI BY ID
	*/
    function loadcoli($cid)
    {
        $query="SELECT COCM_NAME,COLI_UNIT,COLI_COCH_CODE,OCBA_NAME,COLI_TERM,COLI_JDATE
                        FROM cocm INNER JOIN coli ON cocm.COCM_CODE=coli.COLI_COCM_CODE 
                     			INNER JOIN ocba ON ocba.OCBA_CODE=coli.COLI_OCBA_CODE
                               WHERE coli.COLI_COCH_CODE=($cid)";
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
       // echo ($result->info_body);
        return $result;
        
    }
    
    /**
     * GET LIST DOCK IMAGE FROM OCBA_CODE 
    **/
    public function getImgFromCOCH_CODE($cid){
        $qr = "SELECT COCH_IMG FROM coch WHERE COCH_CODE = $cid";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
    
    /**
     * GET USEM_OCBA_CODE FROM USER_ID 
    **/
    public function getOCBA_CODEFromUser($cid){
        $qr = "SELECT USEM_OCBA_CODE FROM #__users WHERE id = $cid";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result->USEM_OCBA_CODE;
    }
}
?>
